﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class Products
    {
        private string DBConnect = ConfigurationManager.ConnectionStrings["SS.Properties.Settings.SSZZConnectionString"].ToString();
        private string SP_GetProducts = "Products_GetProductsByName";
        private string SP_CheckProducts = "Products_CheckProductsByName";
        private string SP_SaveProducts = "Products_SaveProducts";
        private string SP_DeleteProducts = "Products_DeleteProducts";
        private string SP_GetProductNames = "Products_GetProductNames";

        public DataTable GetProducts(string ProductName)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_GetProducts;
            cm.Connection = cn;
            SqlParameter sqlParameter = new SqlParameter("ProductName",ProductName);
            sqlParameter.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public DataTable GetProductNames()
        {
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_GetProductNames;
            cm.Connection = cn;
            SqlDataAdapter da = new SqlDataAdapter(cm);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public bool CheckProducts(string ProductName)
        {
            bool IsExist = false;
            DataTable dt = new DataTable();
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_CheckProducts;
            cm.Connection = cn;
            SqlParameter sqlParameter = new SqlParameter("ProductName", ProductName);
            sqlParameter.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            da.Fill(dt);
            cn.Close();
            if (dt.Rows.Count>0)
            {
                IsExist = true;
            }
            return IsExist;
        }

        public bool SaveProducts(string ProductName, string ProductDescription)
        {
            bool IsSave = true;
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_SaveProducts;
            cm.Connection = cn;
            SqlParameter sqlParameter1 = new SqlParameter("ProductName", ProductName);
            sqlParameter1.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter1);
            SqlParameter sqlParameter2 = new SqlParameter("ProductDescription", ProductDescription);
            sqlParameter2.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter2);
            try
            {
                cn.Open();
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                IsSave = false;
            }
            cn.Close();
            return IsSave;
        }

        public bool DeleteProducts(string IDs)
        {
            bool IsDelete = true;
            SqlConnection cn = new SqlConnection(DBConnect);
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = SP_DeleteProducts;
            cm.Connection = cn;
            SqlParameter sqlParameter1 = new SqlParameter("IDs", IDs);
            sqlParameter1.DbType = DbType.String;
            cm.Parameters.Add(sqlParameter1);
            try
            {
                cn.Open();
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                IsDelete = false;
            }
            cn.Close();
            return IsDelete;
        }
    }
}
